package pers.zxk.jlytnews.dao.impl;

import pers.zxk.jlytnews.dao.WxArticleDao;
import pers.zxk.jlytnews.entity.WxArticle;
import pers.zxk.jlytnews.entity.WxArticleTiny;
import pers.zxk.jlytnews.entity.WxArticleToClient;
import pers.zxk.jlytnews.util.DBconn;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class WxArticleDaoImpl implements WxArticleDao {

    public static final String TABLE_NAME = "tb_wx_article";
    public static final String COL_ID = "id";
    public static final String COL_AID = "aid";
    public static final String COL_APPMSGID = "appmsgid";
    public static final String COL_ITEMIDX = "itemidx";
    public static final String COL_COVER = "cover";
    public static final String COL_LINK = "link";
    public static final String COL_TITLE = "title";
    public static final String COL_CREATE_TIME = "create_time";
    public static final String COL_UPDATE_TIME = "update_time";
    public static final String COL_MEDIA_DURATION = "media_duration";
    public static final String COL_MEDIAAPI_PUBLISH_STATUS = "mediaapi_publish_status";
    public static final String COL_FAKE_ID = "fakeid";


    @Override
    public boolean addWxArticle(String aid, long appmsgid, int itemidx, String cover, String link, String title,
                                int create_time, int update_time, String media_duration, int mediaapi_publish_status,
                                String fakeid) throws SQLException, ClassNotFoundException {
        boolean flag = false;
        DBconn.init();
        String sql = "insert into " + TABLE_NAME + "(" + COL_AID + "," + COL_APPMSGID + "," + COL_ITEMIDX
                + "," + COL_COVER + "," + COL_LINK + "," + COL_TITLE + "," + COL_CREATE_TIME + "," + COL_UPDATE_TIME
                + "," + COL_MEDIA_DURATION + "," + COL_MEDIAAPI_PUBLISH_STATUS + "," + COL_FAKE_ID + ") " +
                "values('" + aid + "','" + appmsgid + "','" + itemidx
                + "','" + cover + "','" + link + "','" + title + "','" + create_time + "','" + update_time
                + "','" + media_duration + "','" + mediaapi_publish_status + "','" + fakeid + "')";
        int i = DBconn.addUpdDel(sql);
        if (i > 0) {
            flag = true;
        }
        DBconn.closeConn();
        return flag;
    }

    @Override
    public boolean delWxArticle(int id) throws SQLException, ClassNotFoundException {
        boolean flag = false;
        DBconn.init();
        String sql = "delete from " + TABLE_NAME + " where " + COL_ID + "=" + id;
        int i = DBconn.addUpdDel(sql);
        if (i > 0) {
            flag = true;
        }
        DBconn.closeConn();
        return flag;
    }

    @Override
    public boolean updateWxArticle(int id, String aid, long appmsgid, int itemidx, String cover, String link,
                                   String title, int create_time, int update_time, String media_duration,
                                   int mediaapi_publish_status, String fakeid) throws SQLException, ClassNotFoundException {
        boolean flag = false;
        DBconn.init();
        String sql = "update " + TABLE_NAME + " set " + COL_AID + " ='" + aid
                + "' , " + COL_APPMSGID + " =" + appmsgid
                + " , " + COL_ITEMIDX + " =" + itemidx
                + " , " + COL_COVER + " ='" + cover
                + "' , " + COL_LINK + " ='" + link
                + "' , " + COL_TITLE + " ='" + title
                + "' , " + COL_CREATE_TIME + " =" + create_time
                + " , " + COL_UPDATE_TIME + " =" + update_time
                + " , " + COL_MEDIA_DURATION + " ='" + media_duration
                + "' , " + COL_MEDIAAPI_PUBLISH_STATUS + " =" + mediaapi_publish_status
                + " , " + COL_FAKE_ID + " ='" + fakeid
                + "' where " + COL_ID + " = " + id;
        int i = DBconn.addUpdDel(sql);
        if (i > 0) {
            flag = true;
        }
        DBconn.closeConn();
        return flag;
    }

    @Override
    public WxArticle getWxArticle(int id) throws SQLException, ClassNotFoundException {
        DBconn.init();
        ResultSet rs = DBconn.selectSql("select * from " + TABLE_NAME + " where " + COL_ID + " = " + id);
        if (rs.next()) {
            WxArticle article = new WxArticle(id,
                    rs.getString(COL_AID),
                    rs.getLong(COL_APPMSGID),
                    rs.getInt(COL_ITEMIDX),
                    rs.getString(COL_COVER),
                    rs.getString(COL_LINK),
                    rs.getString(COL_TITLE),
                    rs.getInt(COL_CREATE_TIME),
                    rs.getInt(COL_UPDATE_TIME),
                    rs.getString(COL_MEDIA_DURATION),
                    rs.getInt(COL_MEDIAAPI_PUBLISH_STATUS),
                    rs.getString(COL_FAKE_ID));
            DBconn.closeConn();
            return article;
        } else {
            DBconn.closeConn();
            throw new SQLException("未找到id为" + id + "的数据");
            //return null;
        }
    }

    @Override
    public List<WxArticle> getLatestWxArticle(int count) throws SQLException, ClassNotFoundException {
        List<WxArticle> list = new ArrayList<WxArticle>();
        DBconn.init();
        ResultSet rs;
        if (count > 0) {
            rs = DBconn.selectSql("select * from " + TABLE_NAME + " order by " + COL_CREATE_TIME + " DESC limit " + count);
        } else if (count == 0) {
            rs = DBconn.selectSql("select * from " + TABLE_NAME + " order by " + COL_CREATE_TIME + " DESC");
        } else {
            throw new SQLException("查询数量不能为负数");
        }
        while (rs.next()) {
            WxArticle article = new WxArticle(rs.getInt(COL_ID),
                    rs.getString(COL_AID),
                    rs.getLong(COL_APPMSGID),
                    rs.getInt(COL_ITEMIDX),
                    rs.getString(COL_COVER),
                    rs.getString(COL_LINK),
                    rs.getString(COL_TITLE),
                    rs.getInt(COL_CREATE_TIME),
                    rs.getInt(COL_UPDATE_TIME),
                    rs.getString(COL_MEDIA_DURATION),
                    rs.getInt(COL_MEDIAAPI_PUBLISH_STATUS),
                    rs.getString(COL_FAKE_ID));
            list.add(article);
        }
        DBconn.closeConn();
        return list;
    }

    /*@Override
    public List<WxArticle> getWxArticleList(int page, int size) throws SQLException, ClassNotFoundException {
        List<WxArticle> list = new ArrayList<WxArticle>();
        DBconn.init();
        ResultSet rs;

        if (size < 0) {
            throw new SQLException("查询数量不能为负数");
        }

        if (page < 0) {//页码小于0时，查询第0页
            page = 0;
        }

        String sql = null;
        if (page == 0 && size == 0) { // page,size  都为0时 查询全部数据
            sql = "select * from " + TABLE_NAME + " order by " + COL_CREATE_TIME + " DESC," + COL_ID + " DESC";
        } else {
            //正常查询：扫描记录数为page*size+size，效率较低，高并发时应优化
            sql = "select * from " + TABLE_NAME + " order by " + COL_CREATE_TIME + " DESC," + COL_ID + " DESC limit " + page * size + "," + size;
            //使用子查询嵌套优化：子查询只返回id速度很快，外层查询扫描记录数为size
            //String sql1 = "select * from tb_news where id>=(select id from tb_news limit " + page * size + ",1) order by date,id DESC limit 0," + size;
        }
        rs = DBconn.selectSql(sql);

        while (rs.next()) {
            WxArticle article = new WxArticle(rs.getInt(COL_ID),
                    rs.getString(COL_AID),
                    rs.getLong(COL_APPMSGID),
                    rs.getInt(COL_ITEMIDX),
                    rs.getString(COL_COVER),
                    rs.getString(COL_LINK),
                    rs.getString(COL_TITLE),
                    rs.getInt(COL_CREATE_TIME),
                    rs.getInt(COL_UPDATE_TIME),
                    rs.getString(COL_MEDIA_DURATION),
                    rs.getInt(COL_MEDIAAPI_PUBLISH_STATUS),
                    rs.getString(COL_FAKE_ID));
            list.add(article);
        }
        DBconn.closeConn();
        return list;
    }*/
    @Override
    public List<WxArticleToClient> getWxArticleList(int page, int size) throws SQLException, ClassNotFoundException {
        List<WxArticleToClient> list = new ArrayList<WxArticleToClient>();
        DBconn.init();
        ResultSet rs;

        if (size < 0) {
            throw new SQLException("查询数量不能为负数");
        }

        if (page < 0) {//页码小于0时，查询第0页
            page = 0;
        }

        String sql = null;
        if (page == 0 && size == 0) { // page,size  都为0时 查询全部数据
            sql = "select * from " + TABLE_NAME + " join " + WxChannelDaoImpl.TABLE_NAME +
                    " where " + TABLE_NAME + "." + COL_FAKE_ID + " = " + WxChannelDaoImpl.TABLE_NAME + "." + COL_FAKE_ID +
                    " order by " + COL_CREATE_TIME + " DESC," + TABLE_NAME + "." + COL_ID + " DESC";
        } else {
            //正常查询：扫描记录数为page*size+size，效率较低，高并发时应优化
            sql = "select * from " + TABLE_NAME + " join " + WxChannelDaoImpl.TABLE_NAME +
                    " where " + TABLE_NAME + "." + COL_FAKE_ID + " = " + WxChannelDaoImpl.TABLE_NAME + "." + COL_FAKE_ID +
                    " order by " + COL_CREATE_TIME + " DESC," + TABLE_NAME + "." + COL_ID + " DESC limit " + page * size + "," + size;
            //使用子查询嵌套优化：子查询只返回id速度很快，外层查询扫描记录数为size
            //String sql1 = "select * from tb_news where id>=(select id from tb_news limit " + page * size + ",1) order by date,id DESC limit 0," + size;
        }
        rs = DBconn.selectSql(sql);

        while (rs.next()) {
            WxArticleToClient article = new WxArticleToClient();
            article.setId(rs.getInt(COL_ID));
            article.setAid(rs.getString(COL_AID));
            article.setAppmsgid(rs.getLong(COL_APPMSGID));
            article.setItemidx(rs.getInt(COL_ITEMIDX));
            article.setCover(rs.getString(COL_COVER));
            article.setLink(rs.getString(COL_LINK));
            article.setTitle(rs.getString(COL_TITLE));
            article.setCreate_time(rs.getInt(COL_CREATE_TIME));
            article.setUpdate_time(rs.getInt(COL_UPDATE_TIME));
            article.setMedia_duration(rs.getString(COL_MEDIA_DURATION));
            article.setMediaapi_publish_status(rs.getInt(COL_MEDIAAPI_PUBLISH_STATUS));
            article.setSourceId(rs.getInt(WxChannelDaoImpl.TABLE_NAME + "." + WxChannelDaoImpl.COL_ID));
            article.setSourceName(rs.getString(WxChannelDaoImpl.TABLE_NAME + "." + WxChannelDaoImpl.COL_TITLE));
            article.setSourceNickname(rs.getString(WxChannelDaoImpl.TABLE_NAME + "." + WxChannelDaoImpl.COL_NICKNAME));
            article.setRound_head_img(rs.getString(WxChannelDaoImpl.TABLE_NAME + "." + WxChannelDaoImpl.COL_ROUND_HEAD_IMG));
            article.setSort(rs.getInt(WxChannelDaoImpl.TABLE_NAME + "." + WxChannelDaoImpl.COL_SORT));
            list.add(article);
        }
        DBconn.closeConn();
        return list;
    }

    @Override
    public List<WxArticleToClient> getWxArticleList(int page, int size, String fakeid) throws SQLException, ClassNotFoundException {
        List<WxArticleToClient> list = new ArrayList<WxArticleToClient>();
        DBconn.init();
        ResultSet rs;

        if (size < 0) {
            throw new SQLException("查询数量不能为负数");
        }

        if (page < 0) {//页码小于0时，查询第0页
            page = 0;
        }

        String sql = null;
        if (page == 0 && size == 0) { // page,size  都为0时 查询全部数据
            sql = "select * from " + TABLE_NAME + " join " + WxChannelDaoImpl.TABLE_NAME +
                    " where " + TABLE_NAME + "." + COL_FAKE_ID + " = " + WxChannelDaoImpl.TABLE_NAME + "." + COL_FAKE_ID +
                    " and " + TABLE_NAME + "." + COL_FAKE_ID + " = '" + fakeid + "' order by " + COL_CREATE_TIME + " DESC," + TABLE_NAME + "." + COL_ID + " DESC";
        } else {
            //正常查询：扫描记录数为page*size+size，效率较低，高并发时应优化
            sql = "select * from " + TABLE_NAME + " join " + WxChannelDaoImpl.TABLE_NAME +
                    " where " + TABLE_NAME + "." + COL_FAKE_ID + " = " + WxChannelDaoImpl.TABLE_NAME + "." + COL_FAKE_ID +
                    " and " + TABLE_NAME + "." + COL_FAKE_ID + " = '" + fakeid + "' order by " + COL_CREATE_TIME + " DESC," + TABLE_NAME + "." + COL_ID + " DESC limit " + page * size + "," + size;
            //使用子查询嵌套优化：子查询只返回id速度很快，外层查询扫描记录数为size
            //String sql1 = "select * from tb_news where id>=(select id from tb_news limit " + page * size + ",1) order by date,id DESC limit 0," + size;
        }
        rs = DBconn.selectSql(sql);

        while (rs.next()) {
            WxArticleToClient article = new WxArticleToClient();
            article.setId(rs.getInt(COL_ID));
            article.setAid(rs.getString(COL_AID));
            article.setAppmsgid(rs.getLong(COL_APPMSGID));
            article.setItemidx(rs.getInt(COL_ITEMIDX));
            article.setCover(rs.getString(COL_COVER));
            article.setLink(rs.getString(COL_LINK));
            article.setTitle(rs.getString(COL_TITLE));
            article.setCreate_time(rs.getInt(COL_CREATE_TIME));
            article.setUpdate_time(rs.getInt(COL_UPDATE_TIME));
            article.setMedia_duration(rs.getString(COL_MEDIA_DURATION));
            article.setMediaapi_publish_status(rs.getInt(COL_MEDIAAPI_PUBLISH_STATUS));
            article.setSourceId(rs.getInt(WxChannelDaoImpl.TABLE_NAME + "." + WxChannelDaoImpl.COL_ID));
            article.setSourceName(rs.getString(WxChannelDaoImpl.TABLE_NAME + "." + WxChannelDaoImpl.COL_TITLE));
            article.setSourceNickname(rs.getString(WxChannelDaoImpl.TABLE_NAME + "." + WxChannelDaoImpl.COL_NICKNAME));
            article.setRound_head_img(rs.getString(WxChannelDaoImpl.TABLE_NAME + "." + WxChannelDaoImpl.COL_ROUND_HEAD_IMG));
            article.setSort(rs.getInt(WxChannelDaoImpl.TABLE_NAME + "." + WxChannelDaoImpl.COL_SORT));
            list.add(article);
        }
        DBconn.closeConn();
        return list;
    }

    @Override
    @Deprecated
    public List<WxArticleTiny> getWxArticleTinyList() throws SQLException, ClassNotFoundException {
        List<WxArticleTiny> list = new ArrayList<>();
        DBconn.init();
        ResultSet rs;

        String sql = "select aid,fakeid from tb_wx_article";
        rs = DBconn.selectSql(sql);

        while (rs.next()) {
            WxArticleTiny article = new WxArticleTiny();
            article.setAid(rs.getString(COL_AID));
            article.setFakeid(rs.getString(COL_FAKE_ID));
            list.add(article);
        }
        DBconn.closeConn();
        return list;
    }

    @Override
    public List<String> getAidList(String fakeid, int startTime, int endTime) throws SQLException, ClassNotFoundException {
        List<String> list = new ArrayList<>();
        DBconn.init();
        ResultSet rs;

        String sql = null;
        if (startTime == 0 && endTime == 0) {
            sql = "select aid from tb_wx_article where fakeid = '" + fakeid + "'";
        } else if (startTime == 0) {
            sql = "select aid from tb_wx_article where fakeid = '" + fakeid +
                    "' and create_time <= " + endTime;
        } else if (endTime == 0) {
            sql = "select aid from tb_wx_article where fakeid = '" + fakeid +
                    "' and create_time >= " + startTime;
        } else {
            sql = "select aid from tb_wx_article where fakeid = '" + fakeid +
                    "' and create_time >= " + startTime + " and create_time <= " + endTime;
        }

        rs = DBconn.selectSql(sql);

        while (rs.next()) {
            String aid = rs.getString(COL_AID);
            list.add(aid);
        }
        DBconn.closeConn();
        return list;
    }
}
